import time
import pymysql
from config import *


def get_time():
    time_str = time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年", "月", "日")


def get_conn():
    """
    :return: 连接，游标
    """
    # 创建连接
    conn = pymysql.connect(host=HOST,
                           user=USER,
                           password=PASSWORD,
                           db=DATABASE,
                           charset="utf8")
    # 创建游标
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    return conn, cursor


# 关闭连接
def close_conn(conn, cursor):
    cursor.close()
    conn.close()


def query(sql, *args):
    """
    封装通用查询
    :param sql:
    :param args:
    :return: 返回查询到的结果，((),(),)的形式
    """
    conn, cursor = get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res


def get_c1_data():
    """
    :return: 返回大屏div id=c1 的数据
    """
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = """
		select 
		confirm,
		confirm_now,
		heal,
		dead
		from history ORDER BY ds desc LIMIT 1
    """
    res = query(sql)
    return res[0]


def get_c2_data():
    """
    :return:  返回各省数据
    """
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select province,confirm_now from details_province where update_time=(select update_time from details_province order by update_time desc limit 1) "
    res = query(sql)
    # print(res)
    return res


def get_l1_data():
    sql = "select ds,confirm_add,confirm_now from history"
    res = query(sql)
    return res


def get_l2_data():
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select end_update_time,province,city,county,address,type" \
          " from risk_area " \
          "where end_update_time=(select end_update_time " \
          "from risk_area " \
          "order by end_update_time desc limit 1) "
    res = query(sql)
    # print(res)
    return res


def get_l3_data():
    sql = """
    select province,heal from details_province 
    where update_time=(select update_time from details_province 
    order by update_time desc limit 1) 
    ORDER BY heal desc limit 10
    """
    res = query(sql)
    # print(res)
    return res


def get_r1_data():
    """
    :return:  返回各地累计确诊
    """
    sql = """
        select province,confirm from details_province 
        where update_time=(select update_time from details_province 
        order by update_time desc limit 1) 
        ORDER BY confirm desc 
    """
    res = query(sql)
    return res


def get_r2_data():
    """
    :return:  返回最近的30条热搜
    """
    sql = 'select content from hotsearch order by id desc limit 30'
    res = query(sql)  # 格式 (('民警抗疫一线奋战16天牺牲 1037364',), ('四川再派两批医疗队 1537382',)
    return res


def get_r3_data():
    """
    :return:  返回海外疫情数据
    """
    sql = """
        SELECT dt,c_name,confirmAdd,confirm,heal,dead FROM world
        where dt=(select dt from world 
        order by dt desc limit 1) 
    """
    res = query(sql)
    return res


def get_ec_centert_confirm_add():
    """
    :return:  返回弹框地图全省市的新增确诊数据
    """
    sql = """
    select province,confirm_add from details_province
    where update_time=(select update_time from details_province 
    order by update_time desc limit 1)
    """
    res = query(sql)
    return res


def get_ec_centert_confirm_now():
    """
    :return:  返回弹框地图全省市的现确诊数据
    """
    sql = """
    select province,confirm_now from details_province
    where update_time=(select update_time from details_province 
    order by update_time desc limit 1)
    """
    res = query(sql)
    return res


def get_ec_centert_xyData():
    """
    :return:  返回弹框地图全省的现确诊数据(动态柱形图)
    """
    sql = """
        select province,confirm_now from details_province 
        where update_time=(select update_time from details_province 
        order by update_time desc limit 1) 
        order by confirm_now asc
    """
    res = query(sql)
    return res


def get_ec_centert_xyData2():
    """
    :return:  返回弹框地图全省的新增确诊数据(动态柱形图)
    """
    sql = """
        select province,confirm_add from details_province 
        where update_time=(select update_time from details_province 
        order by update_time desc limit 1) 
        order by confirm_add asc
    """
    res = query(sql)
    return res


def get_ec_centert_area_rs():
    """
    :return:  返回全省的json地图数据
    """
    sql = """
        select id,c_name,url,fid from area_rs
    """
    res = query(sql)
    return res


def get_ec_centert_qipao(province):
    """
    :return:  返回全省的现确诊的气泡数据
    """
    sql = ""
    if (province != ''):
        sql = """
        select city,confirm_add from details_city
        where update_time=(select update_time from details_city
        order by update_time desc limit 1)
        and city in('地区待确认','境外输入','待确认','外地来津','外地来京','外地来沪','境外来沪','省十里丰监狱','梅河口市','长白山管委会','兵团第九师','第八师石河子','六师五家渠','兵团第十二师','第七师','兵团第四师','经济开发区','涉奥闭环人员辛集市','雄安新区','定州','辛集市','赣江新区','宁东管委会','万盛经开区','两江新区','大渡口区')
        and province like '%%""" + province + """%%'
        """
    else:
        sql = """
        select city,
        cast(SUM(confirm_add) as signed) as confirm_add FROM details_city where  city in('地区待确认','境外输入','待确认')
        GROUP BY city
        """
    res = query(sql)
    return res


def get_lnglat(address):
    """
    :return:  返回各省的中高风险坐标
    """
    sql = "select location,province,city,county,address,id,type,end_update_time from risk_area where end_update_time = (select end_update_time from risk_area order by end_update_time desc limit 1) and city like '%%" + address + "%%'"
    res = query(sql)
    return res


def get_sandian():
    """
    :return:  返回各省的散点坐标
    """
    sql = "select location,province,city,county,address,id,type,end_update_time from risk_area where end_update_time = (select end_update_time from risk_area order by end_update_time desc limit 1) "
    res = query(sql)
    return res


def get_city_data():
    """
    :return:  返回某市区的的数据
    """
    sql = """
    select * from details_city
    where update_time=(select update_time from details_city 
    order by update_time desc limit 1) 
    """
    res = query(sql)
    return res


def get_fudiao_data():
    """
    :return:  返回3d地图旁的移动图标数据
    """
    sql = """
        SELECT cast(sum(confirm_add) as signed) as confirmAdd,
        cast(sum(mediumRiskAreaNum) as signed) as mran,
        cast(sum(highRiskAreaNum)as signed) as hran,
        cast(sum(wzz_add) as signed) as wzzadd 
        FROM details_province
        where update_time=(select update_time from details_province 
        order by update_time desc limit 1) 
    """
    res = query(sql)
    return res


def login(account, password):
    """
    :return:  登录
    """
    sql = "select count(*) from t_user where account =%s and password =%s "
    res = query(sql, account, password)
    return res



